package com.zoe.vhr.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.core.util.ObjectUtil;
import com.zoe.vhr.model.*;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * @Author Zoe.Zhang
 * @Date: 2021/08/11
 * @Description Excel导出
 */
public class POIUtil {
    public static ResponseEntity<byte[]> exportEmpExcel(List<Employee> list) {
        // 1:创建一个excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 配置文档的基本信息
        workbook.createInformationProperties();

        ////////////////////////////////////////////////////////////////////////
        // 2:获取文档的信息
        DocumentSummaryInformation documentSummaryInformation = workbook.getDocumentSummaryInformation();
        // 文档类别
        documentSummaryInformation.setCategory("员工信息");
        // 管理员
        documentSummaryInformation.setManager("zoe");
        // 公司
        documentSummaryInformation.setCompany("www.zoeyyds.top");

        ////////////////////////////////////////////////////////////////////////
        // 3:获取文档摘要信息
        SummaryInformation summaryInformation = workbook.getSummaryInformation();
        // 文档摘要标题
        summaryInformation.setTitle("员工信息表");
        // 文档摘要作者
        summaryInformation.setAuthor("作者:zoe");
        // 文档摘要备注信息
        summaryInformation.setComments("备注信息~");

        ///////////////////////////////////////////////////////////////////////
        // 4:创建样式
        HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
        // 指定背景颜色
        hssfCellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.index);
        hssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 指定日期格式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        ///////////////////////////////////////////////////////////////////////
        // 5:创建一个sheet
        HSSFSheet sheet = workbook.createSheet("员工信息表sheet");
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 5 * 256);
        sheet.setColumnWidth(4, 12 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 10 * 256);
        sheet.setColumnWidth(7, 10 * 256);
        sheet.setColumnWidth(8, 16 * 256);
        sheet.setColumnWidth(9, 12 * 256);
        sheet.setColumnWidth(10, 15 * 256);
        sheet.setColumnWidth(11, 20 * 256);
        sheet.setColumnWidth(12, 16 * 256);
        sheet.setColumnWidth(13, 14 * 256);
        sheet.setColumnWidth(14, 14 * 256);
        sheet.setColumnWidth(15, 12 * 256);
        sheet.setColumnWidth(16, 8 * 256);
        sheet.setColumnWidth(17, 20 * 256);
        sheet.setColumnWidth(18, 20 * 256);
        sheet.setColumnWidth(19, 15 * 256);
        sheet.setColumnWidth(20, 8 * 256);
        sheet.setColumnWidth(21, 25 * 256);
        sheet.setColumnWidth(22, 14 * 256);
        sheet.setColumnWidth(23, 15 * 256);
        sheet.setColumnWidth(24, 15 * 256);
        // 指定第一行为标题行
        HSSFRow row0 = sheet.createRow(0);
        // 指定列的信息
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue("编号");
        cell0.setCellStyle(hssfCellStyle);
        HSSFCell cell1 = row0.createCell(1);
        cell1.setCellValue("姓名");
        cell1.setCellStyle(hssfCellStyle);
        HSSFCell cell2 = row0.createCell(2);
        cell2.setCellValue("性别");
        cell2.setCellStyle(hssfCellStyle);
        HSSFCell cell3 = row0.createCell(3);
        cell3.setCellValue("工号");
        cell3.setCellStyle(hssfCellStyle);
        HSSFCell cell4 = row0.createCell(4);
        cell4.setCellValue("出生日期");
        cell4.setCellStyle(hssfCellStyle);
        HSSFCell cell5 = row0.createCell(5);
        cell5.setCellValue("身份证号码");
        cell5.setCellStyle(hssfCellStyle);
        HSSFCell cell6 = row0.createCell(6);
        cell6.setCellValue("婚姻状况");
        cell6.setCellStyle(hssfCellStyle);
        HSSFCell cell7 = row0.createCell(7);
        cell7.setCellValue("民族");
        cell7.setCellStyle(hssfCellStyle);
        HSSFCell cell8 = row0.createCell(8);
        cell8.setCellValue("籍贯");
        cell8.setCellStyle(hssfCellStyle);
        HSSFCell cell9 = row0.createCell(9);
        cell9.setCellValue("地址");
        cell9.setCellStyle(hssfCellStyle);
        HSSFCell cell10 = row0.createCell(10);
        cell10.setCellValue("政治面貌");
        cell10.setCellStyle(hssfCellStyle);
        HSSFCell cell11 = row0.createCell(11);
        cell11.setCellValue("邮件");
        cell11.setCellStyle(hssfCellStyle);
        HSSFCell cell12 = row0.createCell(12);
        cell12.setCellValue("手机号");
        cell12.setCellStyle(hssfCellStyle);
        HSSFCell cell13 = row0.createCell(13);
        cell13.setCellValue("部门");
        cell13.setCellStyle(hssfCellStyle);
        HSSFCell cell14 = row0.createCell(14);
        cell14.setCellValue("职位");
        cell14.setCellStyle(hssfCellStyle);
        HSSFCell cell15 = row0.createCell(15);
        cell15.setCellValue("职称等级");
        cell15.setCellStyle(hssfCellStyle);
        HSSFCell cell16 = row0.createCell(16);
        cell16.setCellValue("聘用形式");
        cell16.setCellStyle(hssfCellStyle);
        HSSFCell cell17 = row0.createCell(17);
        cell17.setCellValue("入职日期");
        cell17.setCellStyle(hssfCellStyle);
        HSSFCell cell18 = row0.createCell(18);
        cell18.setCellValue("转正日期");
        cell18.setCellStyle(hssfCellStyle);
        HSSFCell cell19 = row0.createCell(19);
        cell19.setCellValue("合同起始日期");
        cell19.setCellStyle(hssfCellStyle);
        HSSFCell cell20 = row0.createCell(20);
        cell20.setCellValue("合同终止日期");
        cell20.setCellStyle(hssfCellStyle);
        HSSFCell cell21 = row0.createCell(21);
        cell21.setCellValue("合同期限");
        cell21.setCellStyle(hssfCellStyle);
        HSSFCell cell22 = row0.createCell(22);
        cell22.setCellValue("最高学历");
        cell22.setCellStyle(hssfCellStyle);
        HSSFCell cell23 = row0.createCell(23);
        cell23.setCellValue("毕业院校");
        cell23.setCellStyle(hssfCellStyle);
        HSSFCell cell24 = row0.createCell(24);
        cell24.setCellValue("专业");
        cell24.setCellStyle(hssfCellStyle);

        if (CollUtil.isNotEmpty(list)) {
            for (int i = 0; i < list.size(); i++) {
                Employee employee = list.get(i);
                HSSFRow row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(employee.getId());
                row.createCell(1).setCellValue(employee.getName());
                row.createCell(2).setCellValue(employee.getGender());
                row.createCell(3).setCellValue(employee.getWorkID());
                HSSFCell c4 = row.createCell(4);
                c4.setCellValue(employee.getBirthday());
                c4.setCellStyle(dateCellStyle);
                row.createCell(5).setCellValue(employee.getIdCard());
                row.createCell(6).setCellValue(employee.getWedlock());
                row.createCell(7).setCellValue(employee.getNation().getName());
                row.createCell(8).setCellValue(employee.getNativePlace());
                row.createCell(9).setCellValue(employee.getAddress());
                row.createCell(10).setCellValue(employee.getPoliticsstatus().getName());
                row.createCell(11).setCellValue(employee.getEmail());
                row.createCell(12).setCellValue(employee.getPhone());
                row.createCell(13).setCellValue(employee.getDepartment().getName());
                row.createCell(14).setCellValue(employee.getPosition().getName());
                row.createCell(15).setCellValue(employee.getjObLevel().getName());
                row.createCell(16).setCellValue(employee.getEngageForm());
                HSSFCell c17 = row.createCell(17);
                c17.setCellStyle(dateCellStyle);
                c17.setCellValue(employee.getBeginDate());
                HSSFCell c18 = row.createCell(18);
                c18.setCellStyle(dateCellStyle);
                c18.setCellValue(employee.getConversionTime());
                HSSFCell c19 = row.createCell(19);
                c19.setCellStyle(dateCellStyle);
                c19.setCellValue(employee.getBeginContract());
                HSSFCell c20 = row.createCell(20);
                c20.setCellStyle(dateCellStyle);
                c20.setCellValue(employee.getEndContract());
                HSSFCell c21 = row.createCell(21);
                c21.setCellValue(employee.getContractTerm());
                row.createCell(22).setCellValue(employee.getTiptopDegree());
                row.createCell(23).setCellValue(employee.getSchool());
                row.createCell(24).setCellValue(employee.getSpecialty());
            }
        }


        try (
            // 获取流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ) {

            HttpHeaders httpHeaders = new HttpHeaders();
            httpHeaders.setContentDispositionFormData("attachment", new String("员工表.xls".getBytes(CharsetUtil.CHARSET_UTF_8), CharsetUtil.ISO_8859_1));
            httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(byteArrayOutputStream);
            return new ResponseEntity<byte[]>(byteArrayOutputStream.toByteArray(), httpHeaders, HttpStatus.CREATED);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(HttpStatus.EXPECTATION_FAILED);
    }

    /**
     * Excel 解析成员工集合
     *
     * @param multipartFile
     * @param listAllNation
     * @param listAllPoliticsStatus
     * @param listAllDepartmentName
     * @param listPositions
     * @param listJobLevels
     */
    public static List<Employee> excelToEmployee(MultipartFile multipartFile, List<Nation> listAllNation, List<Politicsstatus> listAllPoliticsStatus, List<Department> listAllDepartmentName, List<Position> listPositions, List<JObLevel> listJobLevels) {
        List<Employee> empList = CollUtil.newArrayList();
        Employee employee;
        try {
            // 1:创建workbook对象
            HSSFWorkbook workbook = new HSSFWorkbook(multipartFile.getInputStream());
            // 2:获取 workbook中的sheet数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                // 3:获取sheet
                HSSFSheet sheetAt = workbook.getSheetAt(i);

                // 4:获取表单中的行数
                int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();

                for (int i1 = 0; i1 < physicalNumberOfRows; i1++) {
                    // 5:如果是首行则跳出
                    if (i1 == 0) {
                        continue;
                    }

                    // 6:获取行
                    HSSFRow row = sheetAt.getRow(i1);

                    if (ObjectUtil.isNull(row)) {
                        continue;
                    }

                    employee = new Employee();
                    employee.setWorkState("在职");
                    // 7:获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    for (int j = 0; j < physicalNumberOfCells; j++) {
                        HSSFCell cell = row.getCell(j);
                        switch (cell.getCellType()) {
                            case STRING:
                                switch (j) {
                                    case 1:
                                        // 姓名
                                        employee.setName(cell.getStringCellValue());
                                        break;
                                    case 2:
                                        // 性别
                                        employee.setGender(cell.getStringCellValue());
                                        break;
                                    case 3:
                                        // 工号
                                        employee.setWorkID(cell.getStringCellValue());
                                        break;
                                    case 5:
                                        // 身份证号码
                                        employee.setIdCard(cell.getStringCellValue());
                                        break;
                                    case 6:
                                        // 婚姻状态
                                        employee.setWedlock(cell.getStringCellValue());
                                        break;
                                    case 7:
                                        // 民族ID
                                        Integer id = listAllNation.stream().filter(x -> ObjectUtil.equal(x.getName(), cell.getStringCellValue())).findFirst().get().getId();
                                        employee.setNationId(id);
                                        break;
                                    case 8:
                                        // 籍贯
                                        employee.setNativePlace(cell.getStringCellValue());
                                        break;
                                    case 9:
                                        // 地址
                                        employee.setAddress(cell.getStringCellValue());
                                        break;
                                    case 10:
                                        // 政治面貌ID
                                        Integer positionId = listAllPoliticsStatus.stream().filter(x -> ObjectUtil.equal(x.getName(), cell.getStringCellValue())).findFirst().get().getId();
                                        employee.setPoliticId(positionId);
                                        break;
                                    case 11:
                                        // 邮箱
                                        employee.setEmail(cell.getStringCellValue());
                                        break;
                                    case 12:
                                        // 手机号码
                                        employee.setPhone(cell.getStringCellValue());
                                        break;
                                    case 13:
                                        // 部门编号
                                        Integer deptId = listAllDepartmentName.stream().filter(x -> ObjectUtil.equal(x.getName(), cell.getStringCellValue())).findFirst().get().getId();
                                        employee.setDepartmentId(deptId);
                                        break;
                                    case 14:
                                        // 职位
                                        Integer posiId = listPositions.stream().filter(x -> ObjectUtil.equal(x.getName(), cell.getStringCellValue())).findFirst().get().getId();
                                        employee.setPosId(posiId);
                                        break;
                                    case 15:
                                        // 职称等级ID
                                        Integer jobLevelId = listJobLevels.stream().filter(x -> ObjectUtil.equal(x.getName(), cell.getStringCellValue())).findFirst().get().getId();
                                        employee.setJobLevelId(jobLevelId);
                                        break;
                                    case 16:
                                        // 聘用形式
                                        employee.setEngageForm(cell.getStringCellValue());
                                        break;
                                    case 22:
                                        // 最高学历
                                        employee.setTiptopDegree(cell.getStringCellValue());
                                        break;
                                    case 23:
                                        // 毕业院校
                                        employee.setSchool(cell.getStringCellValue());
                                        break;
                                    case 24:
                                        // 专业
                                        employee.setSpecialty(cell.getStringCellValue());
                                        break;
                                    default:
                                        break;
                                }
                                break;
                            default:
                                switch (j) {
                                    case 4:
                                        // 生日
                                        employee.setBirthday(cell.getDateCellValue());
                                        break;
                                    case 17:
                                        // 入职日期
                                        employee.setBeginDate(cell.getDateCellValue());
                                        break;
                                    case 18:
                                        // 转正日期
                                        employee.setConversionTime(cell.getDateCellValue());
                                        break;
                                    case 19:
                                        // 合同起始日期
                                        employee.setBeginContract(cell.getDateCellValue());
                                        break;
                                    case 20:
                                        // 合同结束日期
                                        employee.setEndContract(cell.getDateCellValue());
                                        break;
                                    case 21:
                                        // 合同期限
                                        employee.setContractTerm(cell.getNumericCellValue());
                                        break;
                                    default:
                                        break;
                                }
                                break;
                        }
                    }
                    empList.add(employee);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return empList;
    }
}
